﻿Imports System.Data.SqlClient
Public Class FormStudent

    Public Sub RefreshData()

        Dim cn As New SqlConnection("server=.;uid=sa;pwd=123;database=SchoolDb;")
        cn.Open()

        Dim cmd As New SqlCommand("SELECT s.*, p.PROVINCE_NAME FROM TBL_STUDENT s INNER JOIN TBL_PROVINCE p ON s.PROVINCE_CODE=p.PROVINCE_CODE WHERE UPPER(STUDENT_CODE+NAME+SEX) LIKE UPPER('%" & Me.txtSearch.Text & "%')", cn)
        Dim dr = cmd.ExecuteReader()

        Dim dt As New DataTable()
        dt.Load(dr)
        dr.Close()

        Me.DataGridView1.DataSource = dt

    End Sub
    Private Sub FormStudent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        RefreshData()
    End Sub


    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
        RefreshData()
    End Sub
 
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
         FormStudentEditor.Show() 
    End Sub

    Private Sub btnRemove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
        If Me.DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub
        End If

        If MessageBox.Show("Are you sure want to delete ?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
            Exit Sub
        End If

        Dim row = Me.DataGridView1.SelectedRows(0)
        Dim code = row.Cells(0).Value

        Dim cn As New SqlConnection("server=.;uid=sa;pwd=123;database=SchoolDb;")
        cn.Open()

        Dim cmd As New SqlCommand("DELETE FROM TBL_STUDENT WHERE STUDENT_CODE=N'" & code & "';", cn)
        cmd.ExecuteNonQuery()

        RefreshData()
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

        If Me.DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub
        End If
        Dim row = Me.DataGridView1.SelectedRows(0)
        FormStudentEditor.txtCODE.Enabled = False
        FormStudentEditor.txtCODE.Text = row.Cells("STUDENT_CODE").Value
        FormStudentEditor.txtNAME.Text = row.Cells("_NAME").Value
        FormStudentEditor.cboSEX.SelectedValue = row.Cells("SEX").Value
        FormStudentEditor.dtpDATE.Value = row.Cells("BIRTHDATE").Value
        FormStudentEditor.cboPROVINCE.SelectedValue = row.Cells("PROVINCE_CODE").Value
        FormStudentEditor.txtPHONE.Text = row.Cells("PHONE").Value
        FormStudentEditor.txtAddress.Text = row.Cells("ADDRESS").Value
        FormStudentEditor.Show() 

    End Sub
End Class